/*******************************************************************************
 * Copyright (c) 2009-2011 ETCORE Labs. All rights reserved. 
 * 
 * This software is the confidential and proprietary information of ETCORE Labs.
 * You shall not disclose such confidential information and shall use it
 * only in accordance with the terms of the license agreement you entered into
 * with ETCORE Labs. 
 * 
 * Support can be obtained from support forums at:  http://www.etcore.com/
 * /
 *******************************************************************************/
package etcore.webapp.grouptalk.common.dao;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import etcore.webapp.grouptalk.common.object.Comment;

/**
 * An instance of CommentDAO is a data access object used to access Comment,
 * which contains query and update operations
 * 
 * @author ETCORE GroupTalk Developers
 * 
 */
public class CommentDAO extends BaseDAO {
	/**
	 * Find Comment instances
	 * 
	 * @param begin
	 *            start position
	 * @param length
	 *            count of instances
	 * @return Comment instances
	 */
	@SuppressWarnings("unchecked")
	public List<Comment> findAll(int begin, int length) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List<Comment> results = queryObject.list();

		return results;

	}

	/**
	 * Get Comment instance by id
	 * 
	 * @param id
	 *            instance id
	 * @return Comment instance
	 */
	public Comment findById(java.lang.Integer id) {
		Session session = HibernateSessionFactory.getSession();

		Comment instance = (Comment) session.get(Comment.class.getName(), id);

		return instance;

	}

	/**
	 * Find instances of Comment by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return Comment instances
	 */
	@SuppressWarnings("unchecked")
	public List<Comment> findByProperty(String propertyName, Object value) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag>=0 and STRCMP(now(),model.time)>=0");

		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);

		List<Comment> results = queryObject.list();

		return results;

	}
	@SuppressWarnings("unchecked")
	public List<Comment> findHiddenByProperty(String propertyName, Object value) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag=-1 and STRCMP(now(),model.time)>=0");

		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);

		List<Comment> results = queryObject.list();

		return results;

	}
	
 
	@SuppressWarnings("rawtypes")
	public List  findPublicCommentsByProperty(String propertyName, Object value,
			int begin, int length) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("  select comment_model.id, comment_model.gmid,comment_model.content,comment_model.userName,comment_model.time from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" comment_model,Message message_model, Topic topic_model, Group group_model where comment_model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and comment_model.flag>=0 and comment_model.gmid=message_model.id and message_model.tid=topic_model.id and topic_model.gid=group_model.id and group_model.open>0 and STRCMP(now(),comment_model.time)>=0 order by comment_model.id desc");
 
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List  results = queryObject.list();

		return results;

	}
	/**
	 * Find instances of Comment by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @param begin
	 *            start position
	 * @param length
	 *            count of instances
	 * @return Comment instances
	 */
	@SuppressWarnings("unchecked")
	public List<Comment> findByProperty(String propertyName, Object value,
			int begin, int length) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag>=0 and STRCMP(now(),model.time)>=0 order by time desc");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List<Comment> results = queryObject.list();

		return results;

	}
	@SuppressWarnings("unchecked")
	public List<Comment> findHiddenByProperty(String propertyName, Object value,
			int begin, int length) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag=-1 and STRCMP(now(),model.time)>=0 order by time desc");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		queryObject.setFirstResult(begin);
		queryObject.setMaxResults(length);
		List<Comment> results = queryObject.list();

		return results;

	}
	/**
	 * Find the last comment of a message
	 * 
	 * @param messageID
	 *            message id
	 * @return the last comment of a message
	 */
	public Comment findLastComment(Integer messageID) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer
				.append(" as model where model.gmid=? and STRCMP(now(),model.time)>=0 order by model.id desc ");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, messageID);
		queryObject.setFirstResult(0);
		queryObject.setMaxResults(1);
		Comment result = (Comment) queryObject.uniqueResult();

		return result;

	}

	/**
	 * Find the count of Attachment instances by a property value
	 * 
	 * @param propertyName
	 *            property name
	 * @param value
	 *            property value
	 * @return the count of Attachment instances
	 */
	public long findSizeByProperty(String propertyName, Object value) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag>=0 and STRCMP(now(),model.time)>=0");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		Object results = queryObject.uniqueResult();
		return results == null ? 0 : ((Number) results).longValue();

	}
	public long findHiddenSizeByProperty (String propertyName, Object value) {
		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("select count(*) from ");
		sqlStringBuffer.append(Comment.class.getSimpleName());
		sqlStringBuffer.append(" as model where model.");
		sqlStringBuffer.append(propertyName);
		sqlStringBuffer.append(" = ? and model.flag=-1 and STRCMP(now(),model.time)>=0");
		Query queryObject = session.createQuery(sqlStringBuffer.toString());
		queryObject.setParameter(0, value);
		Object results = queryObject.uniqueResult();
		return results == null ? 0 : ((Number) results).longValue();

	}
	public long findPublicCommentsSizeByProperty(String propertyName,
			Object value) {
		Session session = HibernateSessionFactory.getSession();
		String queryString = "select count(distinct(gt_comment.id)) from gt_comment,gt_message,gt_topic,gt_group where gt_comment."
				+ propertyName
				+ "=? and gt_comment.flag>=0 and gt_comment.gmid=gt_message.id and gt_message.tid=gt_topic.id and gt_topic.gid=gt_group.id and gt_group.open>0 and STRCMP(now(),gt_comment.time)>=0";
		Query queryObject = session.createSQLQuery(queryString);
		queryObject.setParameter(0, value);
		Object results = queryObject.uniqueResult();

		return (results == null) ? 0 : ((Number) results).longValue();

	}

	/**
	 * Find the count of comments in a group
	 * 
	 * @param groupID
	 * @return the count of comments in a group
	 */
	public long searchSizeByGid(Integer groupID) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer
				.append("select count(distinct(gt_comment.id)) as ct from gt_comment,gt_message,gt_topic where gt_comment.gmid=gt_message.id and gt_message.tid=gt_topic.id and gt_topic.gid=");
		sqlStringBuffer.append(groupID);
		sqlStringBuffer.append("  and gt_message.flag>=0 and STRCMP(now(),gt_comment.time)>=0");

		SQLQuery sqlQuery = session.createSQLQuery(sqlStringBuffer.toString())
				.addScalar("ct", Hibernate.INTEGER);

		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();

		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}

	/**
	 * Find the count of comments during the last dayCount days
	 * 
	 * @param groupID
	 *            group id
	 * @param dayCount
	 *            the count of days
	 * @return the count of comments during the last dayCount days
	 */
	public long searchSizeByGidAndDays(Integer groupID, Integer dayCount) {

		Session session = HibernateSessionFactory.getSession();
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer
				.append("select count(distinct(gt_comment.id)) as ct from gt_comment,gt_message,gt_topic,gt_group where  (TO_DAYS(now())-TO_DAYS(gt_comment.time))<");
		sqlStringBuffer.append(dayCount);
		sqlStringBuffer
				.append(" and gt_comment.gmid=gt_message.id and gt_message.tid=gt_topic.id and gt_topic.gid=");
		sqlStringBuffer.append(groupID);
		sqlStringBuffer.append("  and gt_message.flag>=0 and STRCMP(now(),gt_comment.time)>=0");

		SQLQuery sqlQuery = session.createSQLQuery(sqlStringBuffer.toString())
				.addScalar("ct", Hibernate.INTEGER);
		@SuppressWarnings("rawtypes")
		List results = sqlQuery.list();
		return (results == null||results.size()==0)  ? 0 : ((Number) results.get(0)).longValue();

	}

}